using System;
using System.Collections.Generic;
using System.Text;
using DTO;
using System.Data;
using System.Data.SqlClient;

namespace DAO
{
	/// <summary> 
	///Author: nnamthach@gmail.com 
	/// <summary>
    public class ContrDataAccess : Connection
    {
		#region Local Variable
        private string _strSPInsertName = "dbo.[procContr_add]";
        private string _strSPInsertName1 = "dbo.[procContr1_add]";
        private string _strSPUpdateName = "dbo.[procContr_update]";
        private string _strSPUpdateName1 = "dbo.[procContr1_update]";
        private string _strSPDeleteName = "dbo.[procContr_delete]";
        private string _strSPDeleteName1 = "dbo.[procContr1_delete]";
        private string _strSPGetName = "dbo.[procContr_get]";
        private string _strSPGetAllName = "dbo.[procContr_getall]";
        private string _strSPGetAllName1 = "dbo.[procContr1_getall]";
        private string _strSPGettotal = "dbo.[procContr_GetTotal]";
        private string _strSPGettotal1 = "dbo.[procContr_GetTotal1]";
		private string _strSPGetPages = "dbo.[procContr_getpaged]";
		private string _strSPIsExist = "dbo.[procContr_isexist]";
        private string _strSPIsExist1 = "dbo.[procContr1_isexist]";
        private string _strTableName = "Contr";
        private string _strSPGetCondName = "dbo.[procContr_getCond]";
        private string _strSPGetCondName1 = "dbo.[procContr1_getCond]";
        private string _strSPGetAllbgGroup = "dbo.[procContr_getallbgGroup]";
        private string _strSPGetAllbgGroup1 = "dbo.[procContr_getallbgGroup1]";
        private string _strSPUpdateShared = "dbo.[procContr_updateShared]";
        private string _strSPCheckSharedGroup = "dbo.[procContr_CheckSharedGroup]";
        private string _strSPCheckSharedGroup1 = "dbo.[procContr_CheckSharedGroup1]";
        private string _strSPgetSumAmt = "dbo.[procContr_getSumAmt]";
        private string _strSPgetSumAmt1 = "dbo.[procContr1_getSumAmt]";
		#endregion Local Variable
		
		#region Method
        public ContrInfo Get_Contr(
			String Contract,
			Int32 aYear
		, ref string sErr)
        {
			ContrInfo objEntr = new ContrInfo();
            InitConnect();
			InitSPCommand(_strSPGetName);              
			AddParameter("Contract", Contract);
			AddParameter("aYear", aYear);
            
            DataTable list = new DataTable();
            try
            {                
                list = executeSelect();                
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}  
            
            if (list.Rows.Count > 0)
                objEntr = (ContrInfo)GetDataFromDataRow(list, 0);
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            if (sErr != "") ErrorLog.SetLog(sErr);
            return objEntr;
        }

        protected override object GetDataFromDataRow(DataTable dt, int i)
        {
            ContrInfo result = new ContrInfo();
			result.Contract = (dt.Rows[i]["Contract"] == DBNull.Value ? "" : (String)dt.Rows[i]["Contract"]);
            result.LimitAmt = (dt.Rows[i]["LimitAmt"] == DBNull.Value ? 0 : (Double)dt.Rows[i]["LimitAmt"]);
			result.LimitAmt1 = (dt.Rows[i]["LimitAmt1"] == DBNull.Value ? 0 : (Double)dt.Rows[i]["LimitAmt1"]);
            result.CummulativeAmt = (dt.Rows[i]["Requisition"] == DBNull.Value ? 0 : (Double)dt.Rows[i]["Requisition"]);
            result.RemainingAmt = (dt.Rows[i]["Remaining"] == DBNull.Value ? 0 : (Double)dt.Rows[i]["Remaining"]);
			result.ContractHolder = (dt.Rows[i]["ContractHolder"] == DBNull.Value ? "" : (String)dt.Rows[i]["ContractHolder"]);
			result.Desc = (dt.Rows[i]["Desc"] == DBNull.Value ? "" : (String)dt.Rows[i]["Desc"]);
			result.LimitAmtV1 = (dt.Rows[i]["LimitAmtV1"] == DBNull.Value ? 0 : (Double)dt.Rows[i]["LimitAmtV1"]);
			result.LimitAmtV2 = (dt.Rows[i]["LimitAmtV2"] == DBNull.Value ? 0 : (Double)dt.Rows[i]["LimitAmtV2"]);
			result.LimitAmtV3 = (dt.Rows[i]["LimitAmtV3"] == DBNull.Value ? 0 : (Double)dt.Rows[i]["LimitAmtV3"]);
			result.LimitAmtV4 = (dt.Rows[i]["LimitAmtV4"] == DBNull.Value ? 0 : (Double)dt.Rows[i]["LimitAmtV4"]);
			result.LimitAmtV5 = (dt.Rows[i]["LimitAmtV5"] == DBNull.Value ? 0 : (Double)dt.Rows[i]["LimitAmtV5"]);
			result.Desc1V1 = (dt.Rows[i]["Desc1V1"] == DBNull.Value ? "" : (String)dt.Rows[i]["Desc1V1"]);
			result.Desc1V2 = (dt.Rows[i]["Desc1V2"] == DBNull.Value ? "" : (String)dt.Rows[i]["Desc1V2"]);
			result.Desc1V3 = (dt.Rows[i]["Desc1V3"] == DBNull.Value ? "" : (String)dt.Rows[i]["Desc1V3"]);
			result.Desc1V4 = (dt.Rows[i]["Desc1V4"] == DBNull.Value ? "" : (String)dt.Rows[i]["Desc1V4"]);
			result.Desc1V5 = (dt.Rows[i]["Desc1V5"] == DBNull.Value ? "" : (String)dt.Rows[i]["Desc1V5"]);
			result.Desc2V1 = (dt.Rows[i]["Desc2V1"] == DBNull.Value ? "" : (String)dt.Rows[i]["Desc2V1"]);
			result.Desc2V2 = (dt.Rows[i]["Desc2V2"] == DBNull.Value ? "" : (String)dt.Rows[i]["Desc2V2"]);
			result.Desc2V3 = (dt.Rows[i]["Desc2V3"] == DBNull.Value ? "" : (String)dt.Rows[i]["Desc2V3"]);
			result.Desc2V4 = (dt.Rows[i]["Desc2V4"] == DBNull.Value ? "" : (String)dt.Rows[i]["Desc2V4"]);
			result.Desc2V5 = (dt.Rows[i]["Desc2V5"] == DBNull.Value ? "" : (String)dt.Rows[i]["Desc2V5"]);
			result.Date1 = (dt.Rows[i]["Date1"] == DBNull.Value ? DateTime.Now : (DateTime)dt.Rows[i]["Date1"]);
			result.Date2 = (dt.Rows[i]["Date2"] == DBNull.Value ? DateTime.Now : (DateTime)dt.Rows[i]["Date2"]);
			result.Date3 = (dt.Rows[i]["Date3"] == DBNull.Value ? DateTime.Now : (DateTime)dt.Rows[i]["Date3"]);
			result.Date4 = (dt.Rows[i]["Date4"] == DBNull.Value ? DateTime.Now : (DateTime)dt.Rows[i]["Date4"]);
			result.Date5 = (dt.Rows[i]["Date5"] == DBNull.Value ? DateTime.Now : (DateTime)dt.Rows[i]["Date5"]);
            result.aYear = (dt.Rows[i]["Year"] == DBNull.Value ? -1 : (Int32)dt.Rows[i]["Year"]);
            result.BudgetGroup = (dt.Rows[i]["Group"] == DBNull.Value ? "" : (String)dt.Rows[i]["Group"]);
            result.Department = (dt.Rows[i]["Dep"] == DBNull.Value ? "" : (String)dt.Rows[i]["Dep"]);
			result.Employee = (dt.Rows[i]["Employee"] == DBNull.Value ? "" : (String)dt.Rows[i]["Employee"]);
            result.SharedBudget = (dt.Rows[i]["isShared"] == DBNull.Value ? (Int16)0 : (Int16)dt.Rows[i]["isShared"]);
			result.aYearBudgetGroup = (dt.Rows[i]["aYearBudgetGroup"] == DBNull.Value ? "" : (String)dt.Rows[i]["aYearBudgetGroup"]);
			result.ImpDate = (dt.Rows[i]["ImpDate"] == DBNull.Value ? -1 : (Int32)dt.Rows[i]["ImpDate"]);
			result.UpdDate = (dt.Rows[i]["UpdDate"] == DBNull.Value ? -1 : (Int32)dt.Rows[i]["UpdDate"]);
			result.sort1 = (dt.Rows[i]["sort1"] == DBNull.Value ? "" : (String)dt.Rows[i]["sort1"]);
			result.sort2 = (dt.Rows[i]["sort2"] == DBNull.Value ? 0 : (Double)dt.Rows[i]["sort2"]);
           
            return result;
        }

        public DataTable GetAll_Contr(ref string sErr)
        {
            InitConnect();
            InitSPCommand(_strSPGetAllName);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }

        public DataTable GetAll_Contr1(ref string sErr)
        {
            InitConnect();
            InitSPCommand(_strSPGetAllName1);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        public DataTable GetAll_bgGroup(ref string sErr)
        {
            InitConnect();
            InitSPCommand(_strSPGetAllbgGroup);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        public DataTable GetAll_bgGroup1(ref string sErr)
        {
            InitConnect();
            InitSPCommand(_strSPGetAllbgGroup1);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
		/// <summary>
        /// Return 1: Table is exist Identity Field
        /// Return 0: Table is not exist Identity Field
        /// Return -1: Erro
        /// </summary>
        /// <param name="tableName"></param>
        public Int32 Add_Contr(ContrInfo objEntr, ref string sErr)
        {
            int ret = -1;
            openConnection();
            InitSPCommand(_strSPInsertName);           
			AddParameter("Contract", objEntr.Contract);
			AddParameter("LimitAmt", objEntr.LimitAmt);
			AddParameter("LimitAmt1", objEntr.LimitAmt1);
			AddParameter("CummulativeAmt", objEntr.CummulativeAmt);
			AddParameter("RemainingAmt", objEntr.RemainingAmt);
			AddParameter("ContractHolder", objEntr.ContractHolder);
			AddParameter("Desc", objEntr.Desc);
			AddParameter("LimitAmtV1", objEntr.LimitAmtV1);
			AddParameter("LimitAmtV2", objEntr.LimitAmtV2);
			AddParameter("LimitAmtV3", objEntr.LimitAmtV3);
			AddParameter("LimitAmtV4", objEntr.LimitAmtV4);
			AddParameter("LimitAmtV5", objEntr.LimitAmtV5);
			AddParameter("Desc1V1", objEntr.Desc1V1);
			AddParameter("Desc1V2", objEntr.Desc1V2);
			AddParameter("Desc1V3", objEntr.Desc1V3);
			AddParameter("Desc1V4", objEntr.Desc1V4);
			AddParameter("Desc1V5", objEntr.Desc1V5);
			AddParameter("Desc2V1", objEntr.Desc2V1);
			AddParameter("Desc2V2", objEntr.Desc2V2);
			AddParameter("Desc2V3", objEntr.Desc2V3);
			AddParameter("Desc2V4", objEntr.Desc2V4);
			AddParameter("Desc2V5", objEntr.Desc2V5);
            //AddParameter("Date1", objEntr.Date1);
            if (objEntr.Date1 == DateTime.MinValue)
                AddParameter("Date1", DBNull.Value);
            else
                AddParameter("Date1", objEntr.Date1);
            //AddParameter("Date2", objEntr.Date2);
            if (objEntr.Date2 == DateTime.MinValue)
                AddParameter("Date2", DBNull.Value);
            else
                AddParameter("Date2", objEntr.Date2);
            //AddParameter("Date3", objEntr.Date3);
            if (objEntr.Date3 == DateTime.MinValue)
                AddParameter("Date3", DBNull.Value);
            else
                AddParameter("Date3", objEntr.Date3);
            //AddParameter("Date4", objEntr.Date4);
            if (objEntr.Date4 == DateTime.MinValue)
                AddParameter("Date4", DBNull.Value);
            else
                AddParameter("Date4", objEntr.Date4);
            //AddParameter("Date5", objEntr.Date5);
            if (objEntr.Date5 == DateTime.MinValue)
                AddParameter("Date5", DBNull.Value);
            else
                AddParameter("Date5", objEntr.Date5);

			AddParameter("aYear", objEntr.aYear);
			AddParameter("BudgetGroup", objEntr.BudgetGroup);
			AddParameter("Department", objEntr.Department);
			AddParameter("Employee", objEntr.Employee);
			AddParameter("SharedBudget", objEntr.SharedBudget);
			AddParameter("aYearBudgetGroup", objEntr.aYearBudgetGroup);
			AddParameter("ImpDate", objEntr.ImpDate);
			AddParameter("UpdDate", objEntr.UpdDate);
			AddParameter("sort1", objEntr.sort1);
			AddParameter("sort2", objEntr.sort2);
            AddParameter("CreatedBy", objEntr.ModifiedBy);
          
            try
            {
                //command.ExecuteNonQuery();
                object tmp = executeScalar();
                if(tmp != null && tmp != DBNull.Value)
					ret = Convert.ToInt32(tmp);
				else 
					ret=0;
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
			
            return ret;
        }
        public Int32 Add_Contr1(ContrInfo objEntr, ref string sErr)
        {
            int ret = -1;
            openConnection();
            InitSPCommand(_strSPInsertName1);
            AddParameter("Contract", objEntr.Contract);
            AddParameter("LimitAmt", objEntr.LimitAmt);
            AddParameter("LimitAmt1", objEntr.LimitAmt1);
            AddParameter("CummulativeAmt", objEntr.CummulativeAmt);
            AddParameter("RemainingAmt", objEntr.RemainingAmt);
            AddParameter("ContractHolder", objEntr.ContractHolder);
            AddParameter("Desc", objEntr.Desc);
            AddParameter("LimitAmtV1", objEntr.LimitAmtV1);
            AddParameter("LimitAmtV2", objEntr.LimitAmtV2);
            AddParameter("LimitAmtV3", objEntr.LimitAmtV3);
            AddParameter("LimitAmtV4", objEntr.LimitAmtV4);
            AddParameter("LimitAmtV5", objEntr.LimitAmtV5);
            AddParameter("Desc1V1", objEntr.Desc1V1);
            AddParameter("Desc1V2", objEntr.Desc1V2);
            AddParameter("Desc1V3", objEntr.Desc1V3);
            AddParameter("Desc1V4", objEntr.Desc1V4);
            AddParameter("Desc1V5", objEntr.Desc1V5);
            AddParameter("Desc2V1", objEntr.Desc2V1);
            AddParameter("Desc2V2", objEntr.Desc2V2);
            AddParameter("Desc2V3", objEntr.Desc2V3);
            AddParameter("Desc2V4", objEntr.Desc2V4);
            AddParameter("Desc2V5", objEntr.Desc2V5);
            //AddParameter("Date1", objEntr.Date1);
            if (objEntr.Date1 == DateTime.MinValue)
                AddParameter("Date1", DBNull.Value);
            else
                AddParameter("Date1", objEntr.Date1);
            //AddParameter("Date2", objEntr.Date2);
            if (objEntr.Date2 == DateTime.MinValue)
                AddParameter("Date2", DBNull.Value);
            else
                AddParameter("Date2", objEntr.Date2);
            //AddParameter("Date3", objEntr.Date3);
            if (objEntr.Date3 == DateTime.MinValue)
                AddParameter("Date3", DBNull.Value);
            else
                AddParameter("Date3", objEntr.Date3);
            //AddParameter("Date4", objEntr.Date4);
            if (objEntr.Date4 == DateTime.MinValue)
                AddParameter("Date4", DBNull.Value);
            else
                AddParameter("Date4", objEntr.Date4);
            //AddParameter("Date5", objEntr.Date5);
            if (objEntr.Date5 == DateTime.MinValue)
                AddParameter("Date5", DBNull.Value);
            else
                AddParameter("Date5", objEntr.Date5);

            AddParameter("aYear", objEntr.aYear);
            AddParameter("BudgetGroup", objEntr.BudgetGroup);
            AddParameter("Department", objEntr.Department);
            AddParameter("Employee", objEntr.Employee);
            AddParameter("SharedBudget", objEntr.SharedBudget);
            AddParameter("aYearBudgetGroup", objEntr.aYearBudgetGroup);
            AddParameter("ImpDate", objEntr.ImpDate);
            AddParameter("UpdDate", objEntr.UpdDate);
            AddParameter("sort1", objEntr.sort1);
            AddParameter("sort2", objEntr.sort2);
            AddParameter("CreatedBy", objEntr.ModifiedBy);

            try
            {
                //command.ExecuteNonQuery();
                object tmp = executeScalar();
                if (tmp != null && tmp != DBNull.Value)
                    ret = Convert.ToInt32(tmp);
                else
                    ret = 0;
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);

            return ret;
        }

        public string Update_Contr(ContrInfo objEntr)
        {

            openConnection();
            InitSPCommand(_strSPUpdateName); 
                       
			AddParameter("Contract", objEntr.Contract);
			AddParameter("LimitAmt", objEntr.LimitAmt);
			AddParameter("LimitAmt1", objEntr.LimitAmt1);
			AddParameter("CummulativeAmt", objEntr.CummulativeAmt);
			AddParameter("RemainingAmt", objEntr.RemainingAmt);
			AddParameter("ContractHolder", objEntr.ContractHolder);
			AddParameter("Desc", objEntr.Desc);
			AddParameter("LimitAmtV1", objEntr.LimitAmtV1);
			AddParameter("LimitAmtV2", objEntr.LimitAmtV2);
			AddParameter("LimitAmtV3", objEntr.LimitAmtV3);
			AddParameter("LimitAmtV4", objEntr.LimitAmtV4);
			AddParameter("LimitAmtV5", objEntr.LimitAmtV5);
			AddParameter("Desc1V1", objEntr.Desc1V1);
			AddParameter("Desc1V2", objEntr.Desc1V2);
			AddParameter("Desc1V3", objEntr.Desc1V3);
			AddParameter("Desc1V4", objEntr.Desc1V4);
			AddParameter("Desc1V5", objEntr.Desc1V5);
			AddParameter("Desc2V1", objEntr.Desc2V1);
			AddParameter("Desc2V2", objEntr.Desc2V2);
			AddParameter("Desc2V3", objEntr.Desc2V3);
			AddParameter("Desc2V4", objEntr.Desc2V4);
			AddParameter("Desc2V5", objEntr.Desc2V5);
            //AddParameter("Date1", objEntr.Date1);
            if (objEntr.Date1 == DateTime.MinValue)
                AddParameter("Date1", DBNull.Value);
            else
                AddParameter("Date1", objEntr.Date1);
            //AddParameter("Date2", objEntr.Date2);
            if (objEntr.Date2 == DateTime.MinValue)
                AddParameter("Date2", DBNull.Value);
            else
                AddParameter("Date2", objEntr.Date2);
            //AddParameter("Date3", objEntr.Date3);
            if (objEntr.Date3 == DateTime.MinValue)
                AddParameter("Date3", DBNull.Value);
            else
                AddParameter("Date3", objEntr.Date3);
            //AddParameter("Date4", objEntr.Date4);
            if (objEntr.Date4 == DateTime.MinValue)
                AddParameter("Date4", DBNull.Value);
            else
                AddParameter("Date4", objEntr.Date4);
            //AddParameter("Date5", objEntr.Date5);
            if (objEntr.Date5 == DateTime.MinValue)
                AddParameter("Date5", DBNull.Value);
            else
                AddParameter("Date5", objEntr.Date5);

			AddParameter("aYear", objEntr.aYear);
			AddParameter("BudgetGroup", objEntr.BudgetGroup);
			AddParameter("Department", objEntr.Department);
			AddParameter("Employee", objEntr.Employee);
			AddParameter("SharedBudget", objEntr.SharedBudget);
			AddParameter("aYearBudgetGroup", objEntr.aYearBudgetGroup);
			AddParameter("ImpDate", objEntr.ImpDate);
			AddParameter("UpdDate", objEntr.UpdDate);
			AddParameter("sort1", objEntr.sort1);
			AddParameter("sort2", objEntr.sort2);
            AddParameter("ModifiedBy", objEntr.ModifiedBy);
               
            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }
        public string Update_Contr1(ContrInfo objEntr)
        {

            openConnection();
            InitSPCommand(_strSPUpdateName1);

            AddParameter("Contract", objEntr.Contract);
            AddParameter("LimitAmt", objEntr.LimitAmt);
            AddParameter("LimitAmt1", objEntr.LimitAmt1);
            AddParameter("CummulativeAmt", objEntr.CummulativeAmt);
            AddParameter("RemainingAmt", objEntr.RemainingAmt);
            AddParameter("ContractHolder", objEntr.ContractHolder);
            AddParameter("Desc", objEntr.Desc);
            AddParameter("LimitAmtV1", objEntr.LimitAmtV1);
            AddParameter("LimitAmtV2", objEntr.LimitAmtV2);
            AddParameter("LimitAmtV3", objEntr.LimitAmtV3);
            AddParameter("LimitAmtV4", objEntr.LimitAmtV4);
            AddParameter("LimitAmtV5", objEntr.LimitAmtV5);
            AddParameter("Desc1V1", objEntr.Desc1V1);
            AddParameter("Desc1V2", objEntr.Desc1V2);
            AddParameter("Desc1V3", objEntr.Desc1V3);
            AddParameter("Desc1V4", objEntr.Desc1V4);
            AddParameter("Desc1V5", objEntr.Desc1V5);
            AddParameter("Desc2V1", objEntr.Desc2V1);
            AddParameter("Desc2V2", objEntr.Desc2V2);
            AddParameter("Desc2V3", objEntr.Desc2V3);
            AddParameter("Desc2V4", objEntr.Desc2V4);
            AddParameter("Desc2V5", objEntr.Desc2V5);
            //AddParameter("Date1", objEntr.Date1);
            if (objEntr.Date1 == DateTime.MinValue)
                AddParameter("Date1", DBNull.Value);
            else
                AddParameter("Date1", objEntr.Date1);
            //AddParameter("Date2", objEntr.Date2);
            if (objEntr.Date2 == DateTime.MinValue)
                AddParameter("Date2", DBNull.Value);
            else
                AddParameter("Date2", objEntr.Date2);
            //AddParameter("Date3", objEntr.Date3);
            if (objEntr.Date3 == DateTime.MinValue)
                AddParameter("Date3", DBNull.Value);
            else
                AddParameter("Date3", objEntr.Date3);
            //AddParameter("Date4", objEntr.Date4);
            if (objEntr.Date4 == DateTime.MinValue)
                AddParameter("Date4", DBNull.Value);
            else
                AddParameter("Date4", objEntr.Date4);
            //AddParameter("Date5", objEntr.Date5);
            if (objEntr.Date5 == DateTime.MinValue)
                AddParameter("Date5", DBNull.Value);
            else
                AddParameter("Date5", objEntr.Date5);

            AddParameter("aYear", objEntr.aYear);
            AddParameter("BudgetGroup", objEntr.BudgetGroup);
            AddParameter("Department", objEntr.Department);
            AddParameter("Employee", objEntr.Employee);
            AddParameter("SharedBudget", objEntr.SharedBudget);
            AddParameter("aYearBudgetGroup", objEntr.aYearBudgetGroup);
            AddParameter("ImpDate", objEntr.ImpDate);
            AddParameter("UpdDate", objEntr.UpdDate);
            AddParameter("sort1", objEntr.sort1);
            AddParameter("sort2", objEntr.sort2);
            AddParameter("ModifiedBy", objEntr.ModifiedBy);

            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }

        public string Delete_Contr(
			String Contract,
			Int32 aYear
		)
        {
            openConnection();
            InitSPCommand(_strSPDeleteName);                        
			AddParameter("Contract", Contract);
			AddParameter("aYear", aYear);
              
            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }
        public string Delete_Contr1(
            String Contract,
            Int32 aYear
        )
        {
            openConnection();
            InitSPCommand(_strSPDeleteName1);
            AddParameter("Contract", Contract);
            AddParameter("aYear", aYear);

            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }   
		
		
        
        public Boolean IsExist_Contr(
			String Contract,
			Int32 aYear
		)
        {
            InitConnect();
            InitSPCommand(_strSPIsExist);
                       
			AddParameter("Contract", Contract);
			AddParameter("aYear", aYear);
              
            string sErr = "";
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            if(list.Rows.Count==1)
				return true;
            return false;
        }
        public Boolean IsExist_Contr1(
            String Contract,
            Int32 aYear
        )
        {
            InitConnect();
            InitSPCommand(_strSPIsExist1);

            AddParameter("Contract", Contract);
            AddParameter("aYear", aYear);

            string sErr = "";
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            if (list.Rows.Count == 1)
                return true;
            return false;
        }
		
		private string CreateWhereClause(ContrInfo obj)
        {
            String result = "";

            return result;
        }
        
        public DataTable Search(string columnName, string columnValue, string condition, ref string sErr)
        {
            string query = "select * from " + _strTableName + " where " + columnName + " " + condition + " " + columnValue;
            DataTable list = new DataTable();
            openConnection();
            try
            {
                list = executeSelectQuery(query);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            //    if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }

        public DataTable GetCond(string strCond, ref string sErr)
        {
            InitConnect();
            InitSPCommand(_strSPGetCondName);
            AddParameter("Cond", strCond);
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        public DataTable GetCond1(string strCond, ref string sErr)
        {
            InitConnect();
            InitSPCommand(_strSPGetCondName1);
            AddParameter("Cond", strCond);
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        public string Update_Shared(ContrInfo objEntr)
        {

            openConnection();
            InitSPCommand(_strSPUpdateShared);
            AddParameter("Contract", objEntr.Contract);
            AddParameter("aYear", objEntr.aYear);
            AddParameter("SharedBudget", objEntr.SharedBudget);

            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;

        }

        public Boolean IsExist_GroupOtherShared(
            String Contract,
            Int32 aYear,
            String BudgetGroup,
            Int16 SharedBudget
        )
        {
            InitConnect();
            InitSPCommand(_strSPCheckSharedGroup);

            AddParameter("Contract", Contract);
            AddParameter("aYear", aYear);
            AddParameter("BudgetGroup", BudgetGroup);
            AddParameter("SharedBudget", SharedBudget);

            string sErr = "";
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            if (list.Rows.Count >= 1)
                return true;
            return false;
        }
        public Boolean IsExist_GroupOtherShared1(
            String Contract,
            Int32 aYear,
            String BudgetGroup,
            Int16 SharedBudget
        )
        {
            InitConnect();
            InitSPCommand(_strSPCheckSharedGroup1);

            AddParameter("Contract", Contract);
            AddParameter("aYear", aYear);
            AddParameter("BudgetGroup", BudgetGroup);
            AddParameter("SharedBudget", SharedBudget);

            string sErr = "";
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            if (list.Rows.Count >= 1)
                return true;
            return false;
        }

        public DataTable GetTotal(string strCond, ref string sErr)
        {
            InitConnect();
            InitSPCommand(_strSPGettotal);
            AddParameter("Cond", strCond);
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }

        public DataTable GetTotal1(string strCond, ref string sErr)
        {
            InitConnect();
            InitSPCommand(_strSPGettotal1);
            AddParameter("Cond", strCond);
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }

        public string Contr_getSumAmt(
            String Contract,
            Int32 aYear
        )
        {
            string strSum = "0";
            string sErr = "";
            InitConnect();
            InitSPCommand(_strSPgetSumAmt);
            AddParameter("Contract", Contract);
            AddParameter("aYear", aYear);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}


            if (sErr != "") ErrorLog.SetLog(sErr);
            if (list != null)
                if (list.Rows.Count > 0)
                    strSum = list.Rows[0][0].ToString();

            return strSum;
        }
        public string Contr_getSumAmt1(
            String Contract,
            Int32 aYear
        )
        {
            string strSum = "0";
            string sErr = "";
            InitConnect();
            InitSPCommand(_strSPgetSumAmt1);
            AddParameter("Contract", Contract);
            AddParameter("aYear", aYear);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}


            if (sErr != "") ErrorLog.SetLog(sErr);
            if (list != null)
                if (list.Rows.Count > 0)
                    strSum = list.Rows[0][0].ToString();

            return strSum;
        }
		#endregion Method
     
    }
}
